using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;


namespace CodeGenerator.Classes
{
    public class GeneratorDataAccess
    {
        private string mTableName = "";
        private string connStr = "";
        public static string NameSpace = string.Empty;

        public GeneratorDataAccess(string connectionString, string TableName)
        {
            mTableName = TableName;
            connStr = connectionString;
        }

        public  string PropCaseStartLower(string strText)
        {
            string retVal = PropCase(strText);
            retVal = retVal.Substring(0, 1).ToLower() + retVal.Substring(1, (retVal.Length - 1));
            return retVal;
        }

        public  string PropCase(string strText)
        {
            return new CultureInfo("en").TextInfo.ToTitleCase(strText.ToLower());
        }

        public string GeneratDataAccess()
        {
            StringBuilder sql = new StringBuilder();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            string cmdTxt = "select * from  INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_NAME ='"+mTableName+"'";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdTxt;
            cmd.Connection = conn;
            cmd.Dispose();

            SqlDataAdapter oda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            oda.Fill(dt);

            string TABLE_NAME = string.Empty;
            string tType = string.Empty;
            string pkTable = string.Empty;
            string keyType = string.Empty;
            string keyDbType= string.Empty;

            string collName = string.Empty;
            string destColl = string.Empty;
            string parColl = string.Empty;
            string dbType = string.Empty;
            string isNull = string.Empty;
            string updCollPar= string.Empty;
            string collTemp = string.Empty;
            string parTemp = string.Empty;

            string insColl = string.Empty;
            string insCollPar = string.Empty;

            
            foreach (DataRow dr in dt.Rows)
            {

                TABLE_NAME = PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_", "");

                pkTable = GetPK(dr["TABLE_NAME"].ToString());

                cmdTxt = "select data_type from INFORMATION_SCHEMA.COLUMNS where table_name = '" + dr["TABLE_NAME"].ToString() + "' and ordinal_position = 1";

                SqlCommand cmdType = new SqlCommand();
                cmdType.CommandText = cmdTxt;
                cmdType.Connection = conn;

                SqlDataAdapter odaColl = new SqlDataAdapter(cmdType);
                DataTable dtColl = new DataTable();
                odaColl.Fill(dtColl);

                keyType = DecodeType(dtColl.Rows[0][0].ToString());
                keyDbType = DecodeTypeForDB(dtColl.Rows[0][0].ToString());

                cmdTxt = @"SELECT 
					TABLE_CATALOG,
					TABLE_SCHEMA, 
					TABLE_NAME, 
					COLUMN_NAME, 
					ORDINAL_POSITION, 
					COLUMN_DEFAULT AS DefaultSetting, 
					IS_NULLABLE,
					DATA_TYPE, 
					CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
					DATETIME_PRECISION AS DatePrecision,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
				FROM  INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME= '" + dr["TABLE_NAME"].ToString() + "' ORDER BY Ordinal_Position";


                SqlCommand cmdColl = new SqlCommand();
                cmdColl.CommandText = cmdTxt;
                cmdColl.Connection = conn;
                cmdColl.Dispose();

                odaColl = new SqlDataAdapter(cmdColl);
                dtColl = new DataTable();
                odaColl.Fill(dtColl);

                collName = string.Empty;
                destColl = string.Empty;
                parColl = string.Empty;
                dbType = string.Empty;
                isNull = string.Empty;
                updCollPar = string.Empty;
                collTemp = string.Empty;
                parTemp = string.Empty;

                insColl = string.Empty;
                insCollPar = string.Empty;

                foreach (DataRow drColl in dtColl.Rows)
                {
                    collTemp = drColl["COLUMN_NAME"].ToString();
                    parTemp = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_", "");

                    destColl = destColl + ", " + collTemp;
                    parColl = parColl + ", @" + parTemp;

                    int isIdentity = (drColl["IsIdentity"] == null ? 0 : Convert.ToInt32(drColl["IsIdentity"]));

                    if (!(collTemp.ToLower().Contains("create")) && !(collTemp.ToLower().Contains("password")) && isIdentity != 1)
                    {
                        updCollPar = updCollPar + ", " + collTemp + " = @" + parTemp+"\n";
                    }

                    if (isIdentity == 0 && !(collTemp.ToLower().Contains("modified")))
                    {
                        insColl = insColl + ", " +"\n"+ collTemp;
                        insCollPar = insCollPar + ", @" + parTemp+"\n";
                    }

                }

                insColl = insColl.TrimStart(',').Trim();
                insCollPar = insCollPar.TrimStart(',').Trim();

                updCollPar = updCollPar.TrimStart(',').Trim();
                destColl = destColl.TrimStart(',').Trim();
                parColl = parColl.TrimStart(',').Trim();

                #region Begin Class
                //=========================================================
                sql.AppendLine("using System;");
                sql.AppendLine("using System.Collections.Generic;");
                sql.AppendLine("using System.Text;");
                sql.AppendLine("using System.Data;");
                sql.AppendLine("using System.Data.SqlClient;");

                sql.AppendLine("using RTGS.Library;");
                sql.AppendLine("using RTGS.Library.Entities.Rtgs;");
                sql.AppendLine("using RTGS.Library.Enum;");
                sql.AppendLine("using RTGS.Library.Interfaces;");
                sql.AppendLine("namespace "+NameSpace);
                sql.AppendLine("\t{");
                //===========================================================

                //========================================================================
                sql.AppendLine("\t\tpublic partial class " + TABLE_NAME + "Repo : IRepository<" + TABLE_NAME + ", " + keyType + ">");
                sql.AppendLine("\t\t{");
                sql.AppendLine("\t\t\tprivate string errorLog;");
                sql.AppendLine("\t\t\tprivate string connectionString;");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tpublic " + TABLE_NAME + "Repo(string ConnectionString)");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\terrorLog = String.Empty;");
                sql.AppendLine("\t\t\t\tconnectionString = ConnectionString;");
                sql.AppendLine("\t\t\t}");
                //==========================================================================

                #endregion

                #region Method Scalar
                //METHOD SELECT SCALAR
                //===============================================================================
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tpublic object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\t\tobject _result = null;");
                sql.AppendLine("\t\t\t\t\tusing (SqlConnection conn = new SqlConnection(connectionString))");
                sql.AppendLine("\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\tusing (SqlCommand cmd = conn.CreateCommand())");
                sql.AppendLine("\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\tStringBuilder sbQuery = new StringBuilder();");
                sql.AppendLine("\t\t\t\t\t\t\tswitch (function)");
                sql.AppendLine("\t\t\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\t\tcase SQL.Function.Aggregate.Max:");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "SELECT MAX({0}) FROM " + dr["TABLE_NAME"].ToString() + "\"" + ", column);");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tbreak;");
                sql.AppendLine("\t\t\t\t\t\t\t\tcase SQL.Function.Aggregate.Min:");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "SELECT MIN({0}) FROM " + dr["TABLE_NAME"].ToString() + "\"" + ", column);");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tbreak;");
                sql.AppendLine("\t\t\t\t\t\t\t\tcase SQL.Function.Aggregate.Distinct:");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "SELECT DISTINCT({0}) FROM " + dr["TABLE_NAME"].ToString() + "\"" + ", column);");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tbreak;");
                sql.AppendLine("\t\t\t\t\t\t\t\tcase SQL.Function.Aggregate.Count:");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "SELECT COUNT({0}) FROM " + dr["TABLE_NAME"].ToString() + "\"" + ", column);");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tbreak;");
                sql.AppendLine("\t\t\t\t\t\t\t\tcase SQL.Function.Aggregate.Sum:");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "SELECT SUM({0}) FROM " + dr["TABLE_NAME"].ToString() + "\"" + ", column);");
                sql.AppendLine("\t\t\t\t\t\t\t\t\t\tbreak;");
                sql.AppendLine("\t\t\t\t\t\t\t\tcase SQL.Function.Aggregate.Avg:");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "SELECT AVG({0}) FROM " + dr["TABLE_NAME"].ToString() + "\"" + ", column);");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tbreak;");
                sql.AppendLine("\t\t\t\t\t\t\t\tdefault:");
                sql.AppendLine("\t\t\t\t\t\t\t\t// do nothing ");
                sql.AppendLine("\t\t\t\t\t\t\t\t\tbreak;");
                sql.AppendLine("\t\t\t\t\t\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\t\t\t\t\tcmd.CommandText = sbQuery.ToString();");
                sql.AppendLine("\t\t\t\t\t\t\tcmd.CommandType = CommandType.Text;");
                sql.AppendLine("\t\t\t\t\t\t\ttry");
                sql.AppendLine("\t\t\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\t\tconn.Open();");
                sql.AppendLine("\t\t\t\t\t\t\t\t_result = cmd.ExecuteScalar();");
                sql.AppendLine("\t\t\t\t\t\t\t}");
                sql.AppendLine("\t\t\t\t\t\t\tcatch (Exception ex)");
                sql.AppendLine("\t\t\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\t\terrorLog = ex.Message;");
                sql.AppendLine("\t\t\t\t\t\t\t}");
                sql.AppendLine("\t\t\t\t\t\t\tfinally");
                sql.AppendLine("\t\t\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();");
                sql.AppendLine("\t\t\t\t\t\t\t}");
                sql.AppendLine("\t\t\t\t\t}");
                sql.AppendLine("\t\t\t\t\t}");
                sql.AppendLine("\t\t\treturn _result;");
                sql.AppendLine("\t\t}");
                //==================================================================================
                #endregion

                #region Method GetList()

                //METHOD GETLIST
                //==================================================================================
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tpublic List<" + TABLE_NAME + "> GetList()");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\tList<" + TABLE_NAME + "> tt = new List<" + TABLE_NAME + ">();");
                sql.AppendLine("\t\t\t\tusing (SqlConnection conn = new SqlConnection(connectionString))");
                sql.AppendLine("\t\t\t\t{");
                sql.AppendLine("\t\t\t\tusing (SqlCommand cmd = conn.CreateCommand())");
                sql.AppendLine("\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\tcmd.CommandText = " + "\"" + "SELECT " + destColl + " FROM " + dr["TABLE_NAME"].ToString() + "\"" + ";");
                sql.AppendLine("\t\t\t\t\tcmd.CommandType = CommandType.Text;");
                sql.AppendLine("\t\t\t\t\ttry");
                sql.AppendLine("\t\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\tconn.Open();");
                sql.AppendLine("\t\t\t\t\t\t\tusing (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))");
                sql.AppendLine("\t\t\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\t\twhile (dr.Read())");
                sql.AppendLine("\t\t\t\t\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\t\t\t\t\t"+TABLE_NAME + " t = new " + TABLE_NAME + "();");

                foreach (DataRow drColl in dtColl.Rows)
                {
                    collName = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_","");
                    dbType = DecodeType(drColl["DATA_TYPE"].ToString());
                    isNull = drColl["IS_NULLABLE"].ToString()=="NO"?"":"?";

                    sql.AppendLine("\t\t\t\t\t\t\t\t\t"+SetDomainValue(TABLE_NAME, collName, dbType, isNull));

                   
                }

               sql.AppendLine("\n");
               sql.AppendLine("\t\t\t\t\t\t\t\t\ttt.Add(t);");
               sql.AppendLine("\t\t\t\t\t\t\t\t\tt.Dispose();");
               sql.AppendLine("\t\t\t\t\t\t\t\t}");
               sql.AppendLine("\t\t\t\t\t\t\t}");
               sql.AppendLine("\t\t\t\t\t\t");
               sql.AppendLine("\t\t\t\t\t\t}");
               sql.AppendLine("\t\t\t\t\tcatch (Exception ex)");
               sql.AppendLine("\t\t\t\t\t{");
               sql.AppendLine("\t\t\t\t\t\terrorLog = ex.Message;");
               sql.AppendLine("\t\t\t\t\t}");
               sql.AppendLine("\t\t\t\t\tfinally");
               sql.AppendLine("\t\t\t\t\t{");
               sql.AppendLine("\t\t\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();");
               sql.AppendLine("\t\t\t\t\t}");
               sql.AppendLine("\t\t\t}");
               sql.AppendLine("\t\t}");
               sql.AppendLine("\t\t\treturn tt;");
               sql.AppendLine("\t\t}");

               //===============================================================

                #endregion

                #region Method GetList() WithParam

               //METHOD GetList WITH PARAM
            //=======================================================================
             sql.AppendLine("\n");
             sql.AppendLine("\t\t\tpublic List<" + TABLE_NAME + "> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)");
             sql.AppendLine("\t\t\t{");
             sql.AppendLine("\t\t\t\tList<" + TABLE_NAME + "> tt = new List<" + TABLE_NAME + ">();");
             sql.AppendLine("\t\t\t\tusing (SqlConnection conn = new SqlConnection(connectionString))");
             sql.AppendLine("\t\t\t\t{");
             sql.AppendLine("\t\t\t\t\tusing (SqlCommand cmd = conn.CreateCommand())");
             sql.AppendLine("\t\t\t\t\t{");
             sql.AppendLine("\t\t\t\t\t\tint startRow = ((currentPage - 1) * pageSize);");
             sql.AppendLine("\t\t\t\t\t\tint endRow = (startRow + pageSize);");

             sql.AppendLine("\t\t\t\t\t\tStringBuilder sbQuery = new StringBuilder();");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + " SELECT * " + "\"" + ");");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + " FROM (SELECT TOP({0}) " + "\"" + ", pageSize);");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "r.* " + "\"" + ");");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "FROM (SELECT TOP({0} + {1} - 1) " + destColl + "\"" + ", startRow, pageSize);");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + " FROM " + dr["TABLE_NAME"].ToString() + "\"" + ");");
             sql.Append("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "{0} " + "\"" + ", parameters);");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "ORDER BY {0} {1}) r " + "\"" + ", sortName, sortOrder);");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "ORDER BY {0} DESC) r " + "\"" + ", sortName);");
             sql.AppendLine("\t\t\t\t\t\tsbQuery.AppendFormat(" + "\"" + "ORDER BY {0}" + "\"" + ", sortName);");

             sql.AppendLine("\t\t\t\t\t\tcmd.CommandText = sbQuery.ToString();");
             sql.AppendLine("\t\t\t\t\t\tcmd.CommandType = CommandType.Text;");
             sql.AppendLine("\t\t\t\t\t\ttry");
             sql.AppendLine("\t\t\t\t\t\t{");
             sql.AppendLine("\t\t\t\t\t\t\tconn.Open();");
             sql.AppendLine("\t\t\t\t\t\t\tusing (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))");
             sql.AppendLine("\t\t\t\t\t\t\t{");
             sql.AppendLine("\t\t\t\t\t\t\t\twhile (dr.Read())");
             sql.AppendLine("\t\t\t\t\t\t\t\t{");
             sql.AppendLine("\t\t\t\t\t\t\t\t\t"+TABLE_NAME + " t = new " + TABLE_NAME + "();");

                foreach (DataRow drColl in dtColl.Rows)
            {
                collName = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_","");
                dbType = DecodeType(drColl["DATA_TYPE"].ToString());
                isNull = drColl["IS_NULLABLE"].ToString()=="NO"?"":"?";

                sql.AppendLine("\t\t\t\t\t\t\t\t\t"+SetDomainValue(TABLE_NAME,collName,dbType,isNull));
               
            }

          sql.AppendLine("\n");
          sql.AppendLine("\t\t\t\t\t\t\t\t\ttt.Add(t);");
          sql.AppendLine("\t\t\t\t\t\t\t\t\tt.Dispose();");

          sql.AppendLine("\t\t\t\t\t\t\t\t}");
          sql.AppendLine("\t\t\t\t\t\t\t}");
          sql.AppendLine("\t\t\t\t\t\t}");
          sql.AppendLine("\t\t\t\t\t\tcatch (Exception ex)");
          sql.AppendLine("\t\t\t\t\t\t\t{");
          sql.AppendLine("\t\t\t\t\t\t\t\terrorLog = ex.Message;");
          sql.AppendLine("\t\t\t\t\t\t\t}");
          sql.AppendLine("\t\t\t\t\t\tfinally");
          sql.AppendLine("\t\t\t\t\t\t\t{");
          sql.AppendLine("\t\t\t\t\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();");
          sql.AppendLine("\t\t\t\t\t\t\t}");
          sql.AppendLine("\t\t\t\t\t}");
          sql.AppendLine("\t\t\t\t}");
          sql.AppendLine("\t\t\t\treturn tt;");
          sql.AppendLine("\t\t\t}");
          //=============================================================
               #endregion

                #region Method GetByID()

          //METHOD GETBYID

          //================================================================

          sql.AppendLine("\n");
          sql.AppendLine("\t\t\tpublic " + TABLE_NAME + " GetById(" + keyType + " key)");
          sql.AppendLine("\t\t\t{");
          sql.AppendLine("\t\t\t\t"+TABLE_NAME + "  t = new " + TABLE_NAME + "();");
          sql.AppendLine("\t\t\t\tusing (SqlConnection conn = new SqlConnection(connectionString))");
          sql.AppendLine("\t\t\t\t{");
          sql.AppendLine("\t\t\t\t\tusing (SqlCommand cmd = conn.CreateCommand())");
          sql.AppendLine("\t\t\t\t\t{");
          sql.AppendLine("\t\t\t\t\t\tcmd.CommandText = " + "\"" + " SELECT " + destColl + " FROM " + dr["TABLE_NAME"].ToString() + " WHERE " + pkTable + " = @" + PropCaseStartLower(pkTable).Replace("_", "") + "\"" + ";");
          sql.AppendLine("\t\t\t\t\t\tcmd.CommandType = CommandType.Text;");
          sql.AppendLine("\t\t\t\t\t\tcmd.Parameters.Add(" + "\"" + "@" + PropCaseStartLower(pkTable).Replace("_", "") + "\"" + ", SqlDbType." + keyDbType + ").Value = key;");
          sql.AppendLine("\t\t\t\t\t\ttry");
          sql.AppendLine("\t\t\t\t\t\t{");
          sql.AppendLine("\t\t\t\t\t\t\tconn.Open();");
          sql.AppendLine("\t\t\t\t\t\t\tusing (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))");
          sql.AppendLine("\t\t\t\t\t\t\t{");
          sql.AppendLine("\t\t\t\t\t\t\t\twhile (dr.Read())");
          sql.AppendLine("\t\t\t\t\t\t\t\t{");


                foreach (DataRow drColl in dtColl.Rows)
        {
            collName = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_","");
            dbType = DecodeType(drColl["DATA_TYPE"].ToString());
            isNull = drColl["IS_NULLABLE"].ToString()=="NO"?"":"?";

            sql.AppendLine("\t\t\t\t\t\t\t\t\t"+SetDomainValue(TABLE_NAME,collName,dbType,isNull));

         }

         sql.AppendLine("\t\t\t\t\t\t\t\t}");
         sql.AppendLine("\t\t\t\t\t\t\t}");
         sql.AppendLine("\t\t\t\t\t\t}");
         sql.AppendLine("\t\t\t\t\t\tcatch (Exception ex)");
         sql.AppendLine("\t\t\t\t\t\t{");
         sql.AppendLine("\t\t\t\t\t\t\terrorLog = ex.Message;");
         sql.AppendLine("\t\t\t\t\t\t}");
         sql.AppendLine("\t\t\t\t\t\tfinally");
         sql.AppendLine("\t\t\t\t\t\t{");
         sql.AppendLine("\t\t\t\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();");
         sql.AppendLine("\t\t\t\t\t\t}");
         sql.AppendLine("\t\t\t\t\t}");
         sql.AppendLine("\t\t\t\t}");
         sql.AppendLine("\t\t\treturn t;");
         sql.AppendLine("\t\t\t}");
         //============================================================

          #endregion

                #region Method Save()

         //METHOD SAVE
         //=======================================================================
         sql.AppendLine("\n");
         sql.AppendLine("\t\t\tpublic bool Save(" + TABLE_NAME + " domain)");
         sql.AppendLine("\t\t\t{");
         sql.AppendLine("\t\t\t\tbool result = false;");
         sql.AppendLine("\t\t\t\tusing (SqlConnection conn = new SqlConnection(connectionString))");
         sql.AppendLine("\t\t\t\t{");
         sql.AppendLine("\t\t\t\t\tusing (SqlCommand cmd = conn.CreateCommand())");
         sql.AppendLine("\t\t\t\t\t{");
         sql.AppendLine("\t\t\t\t\t\tcmd.CommandText = @" + "\"" + " INSERT INTO " + dr["TABLE_NAME"].ToString() + "(" +"\n"+ (insColl) + "\n"+")"+"\n"+ "VALUES("+"\n"+insCollPar +"\n"+ ")"+"\""+";");
         sql.AppendLine("\t\t\t\t\t\tcmd.CommandType = CommandType.Text;");


                foreach (DataRow drColl in dtColl.Rows)
            {
                collName = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_","");
               if ((drColl["IsIdentity"].ToString() != "1") && !(drColl["COLUMN_NAME"].ToString().ToLower().Contains("modified")))
                {
                  string isNullable = (drColl["IS_NULLABLE"].ToString()=="NO") ? "domain." + collName : "(object)domain." + collName + " ?? DBNull.Value";

                  sql.AppendLine("\t\t\t\t\t\tcmd.Parameters.Add(" + "\"" + "@" + collName + "\"" + ", SqlDbType." + DecodeTypeForDB(drColl["DATA_TYPE"].ToString()) + ").Value =" + isNullable + ";");

               }
            }
        


            sql.AppendLine("\t\t\t\t\t\ttry");
            sql.AppendLine("\t\t\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\t\t\tconn.Open();");
            sql.AppendLine("\t\t\t\t\t\t\tcmd.ExecuteNonQuery();");
            sql.AppendLine("\t\t\t\t\t\t\tresult = true;");
            sql.AppendLine("\t\t\t\t\t\t}");
            sql.AppendLine("\t\t\t\t\t\tcatch (Exception ex)");
            sql.AppendLine("\t\t\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\t\t\terrorLog = ex.Message;");
            sql.AppendLine("\t\t\t\t\t\t}");
            sql.AppendLine("\t\t\t\t\t\tfinally");
            sql.AppendLine("\t\t\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();");
            sql.AppendLine("\t\t\t\t\t\t}");
            sql.AppendLine("\t\t\t\t\t}");
            sql.AppendLine("\t\t\t\t}");
            sql.AppendLine("\t\t\treturn result;");
            sql.AppendLine("\t\t\t}");
            //==========================================================================
#endregion

                #region Method Update()
            //METHOD UPDATE
             //==========================================================================
            sql.AppendLine("\n");
            sql.AppendLine("\t\t\tpublic bool Update(" + TABLE_NAME + " domain)");
            sql.AppendLine("\t\t\t{");
            sql.AppendLine("\t\t\t\tint result = 0;");
            sql.AppendLine("\t\t\t\tusing (SqlConnection conn = new SqlConnection(connectionString))");
            sql.AppendLine("\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\tusing (SqlCommand cmd = conn.CreateCommand())");
            sql.AppendLine("\t\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\t\tcmd.CommandText = @" + "\"" + "UPDATE " + dr["TABLE_NAME"].ToString() + " SET " +"\n" + updCollPar + "\n"+" WHERE " + pkTable + "= @" + PropCaseStartLower(pkTable).Replace("_", "") + "\"" + ";");
            sql.AppendLine("\t\t\t\t\t\tcmd.CommandType = CommandType.Text;");


             foreach (DataRow drColl in dtColl.Rows)
                {
                    collName = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_","");
                    if (!(drColl["COLUMN_NAME"].ToString().ToLower().Contains("create")) && !(drColl["COLUMN_NAME"].ToString().ToLower().Contains("password")))
                    {
                        string isNullable = (drColl["IS_NULLABLE"].ToString()=="NO") ? "domain." + collName : "(object)domain." + collName + " ?? DBNull.Value";

                        sql.AppendLine("\t\t\t\t\t\tcmd.Parameters.Add(" + "\"" + "@" + collName +"\""+ ", SqlDbType." + DecodeTypeForDB(drColl["DATA_TYPE"].ToString()) + ").Value =" + isNullable + ";");
			        }
                 }


                 sql.AppendLine("\n");
                 sql.AppendLine("\t\t\t\t\t\ttry");
                 sql.AppendLine("\t\t\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\t\t\tconn.Open();");
                 sql.AppendLine("\t\t\t\t\t\t\tresult = cmd.ExecuteNonQuery();");
                 sql.AppendLine("\t\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t\t\tcatch (Exception ex)");
                 sql.AppendLine("\t\t\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\t\t\terrorLog = ex.Message;");
                 sql.AppendLine("\t\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t\t\tfinally");
                 sql.AppendLine("\t\t\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();");
                 sql.AppendLine("\t\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t}");
                 sql.AppendLine("\t\t\treturn (result > 0);");
                 sql.AppendLine("\t\t\t}");

                 //==================================================================
            #endregion

                #region Method Delete()
                 //METHOD DELETE
                //==================================================================
                 sql.AppendLine("\n");
                 sql.AppendLine("\t\t\tpublic bool Delete(" + keyType + " key)");
                 sql.AppendLine("\t\t\t{");
                 sql.AppendLine("\t\t\t\tbool result = false;");
                 sql.AppendLine("\t\t\t\tusing (SqlConnection conn = new SqlConnection(connectionString))");
                 sql.AppendLine("\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\tusing (SqlCommand cmd = conn.CreateCommand())");
                 sql.AppendLine("\t\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\t\tcmd.CommandText = " + "\"" + "DELETE FROM " + dr["TABLE_NAME"].ToString() + " WHERE " + pkTable + "= @" + PropCaseStartLower(pkTable).Replace("_", "") + "\"" + ";");
                 sql.AppendLine("\t\t\t\t\t\tcmd.CommandType = CommandType.Text;");
                 sql.AppendLine("\t\t\t\t\t\tcmd.Parameters.Add(" + "\"" + "@" + PropCaseStartLower(pkTable).Replace("_", "") + "\""+", SqlDbType.Int).Value = key;");
                 sql.AppendLine("\n");
                 sql.AppendLine("\t\t\t\t\t\ttry");
                 sql.AppendLine("\t\t\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\t\t\tconn.Open();");
                 sql.AppendLine("\t\t\t\t\t\t\tcmd.ExecuteNonQuery();");
                 sql.AppendLine("\t\t\t\t\t\t\tresult = true;");
                 sql.AppendLine("\t\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t\t\tcatch (Exception ex)");
                 sql.AppendLine("\t\t\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\t\t\terrorLog = ex.Message;");
                 sql.AppendLine("\t\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t\t\tfinally");
                 sql.AppendLine("\t\t\t\t\t\t{");
                 sql.AppendLine("\t\t\t\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();");
                 sql.AppendLine("\t\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t\t}");
                 sql.AppendLine("\t\t\t\t}");
                 sql.AppendLine("\t\t\treturn result;");
                 sql.AppendLine("\t\t\t}");
                 //=======================================
                 #endregion

                #region Get Error Message()
                 //METHOD GET ERROR MESSAGE
                //=========================================
                 sql.AppendLine("\n");
                 sql.AppendLine("\t\t\tpublic string GetErrorMessage()");
                 sql.AppendLine("\t\t\t{");
                 sql.AppendLine("\t\t\t\treturn errorLog;");
                 sql.AppendLine("\t\t\t}");
                 //=========================================
                 #endregion

                #region End Class
                 //PENUTUP CLASS DAN NAMESPACE
                //==================================
                 sql.AppendLine("\n");
                 sql.AppendLine("\t\t}");
                 sql.AppendLine("\t}");
                 //=======================================
                 #endregion
             }

            return sql.ToString();
        }

        private string DecodeTypeNullable(string dbType, string nullable)
        {
            string retVal = DecodeType(dbType);
            string isNull = nullable == "NO" ? "" : "?";

            if (retVal == "string")
            {
            }
            else if (retVal == "byte[]")
            {
            }
            else
            {
                retVal = retVal + isNull;
            }
            return retVal;
        }
        private string DecodeType(string sqlType)
        {
            sqlType = sqlType.ToLower();
            string sysType = "string";
            switch (sqlType)
            {
                case "bigint":
                    sysType = "long";
                    break;
                case "smallint":
                    sysType = "short";
                    break;
                case "int":
                    sysType = "int";
                    break;
                case "uniqueidentifier":
                    sysType = "Guid";
                    break;
                case "smalldatetime":
                case "datetime":
                case "date":
                    sysType = "DateTime";
                    break;
                case "float":
                    sysType = "double";
                    break;
                case "real":
                case "numeric":
                case "smallmoney":
                case "decimal":
                case "money":
                    sysType = "decimal";
                    break;
                case "tinyint":
                    sysType = "byte";
                    break;
                case "bit":
                    sysType = "bool";
                    break;
                case "image":
                case "binary":
                case "varbinary":
                case "timestamp":
                    sysType = "byte[]";
                    break;
            }
            return sysType;
        }
        private string DecodeTypeForDB(string sqlType)
        {
            switch (sqlType)
            {
                case "varchar":
                    return "VarChar";
                case "nvarchar":
                    return "NVarChar";
                case "int":
                    return "Int";
                case "uniqueidentifier":
                    return "UniqueIdentifier";
                case "datetime":
                    return "DateTime";
                case "bigint":
                    return "BigInt";
                case "binary":
                    return ".Binary";
                case "bit":
                    return "Bit";
                case "char":
                    return "Char";
                case "decimal":
                    return "Decimal";
                case "float":
                    return "Float";
                case "image":
                    return "Image";
                case "money":
                    return "Money";
                case "nchar":
                    return "NChar";
                case "ntext":
                    return "NText";
                case "numeric":
                    return "Decimal";
                case "real":
                    return "Real";
                case "smalldatetime":
                    return "SmallDateTime";
                case "smallint":
                    return "SmallInt";
                case "smallmoney":
                    return "SmallMoney";
                case "sql_variant":
                    return "Variant";
                case "sysname":
                    return "VarChar";
                case "text":
                    return "Text";
                case "timestamp":
                    return "Timestamp";
                case "tinyint":
                    return "TinyInt";
                case "varbinary":
                    return "VarBinary";
                case "xml":
                    return "Xml";
                default:
                    return "VarChar";
            }
        }

        
        private string SetDomainValue(string TABLE_NAME, string collName, string dbType, string isNull)
        {
            string retVal = string.Empty;
            if (dbType.ToLower() == "string")
            {
                retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? string.Empty : " + "dr[" + TABLE_NAME + ".Fields." + collName + "].ToString();";
            }
            else if (dbType.ToLower() == "datetime")
            {
                if (isNull == "?")
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? (DateTime?)null : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
                else
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? DateTime.Now : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
            }
            else if (dbType.ToLower() == "int")
            {
                if (isNull == "?")
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? (int?)null : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
                else
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? 0 : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
            }
            else if (dbType.ToLower() == "decimal")
            {
                if (isNull == "?")
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? (decimal?)null : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
                else
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? 0 : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
            }
            else if (dbType.ToLower() == "bool")
            {
                if (isNull == "?")
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? (bool?)null : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
                else
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? 0 : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
            }
            else if (dbType.ToLower() == "short")
            {
                if (isNull == "?")
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? (short?)null : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
                else
                {
                    retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? 0 : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
                }
            }
            else
            {
                retVal = "t." + collName + " = dr[" + TABLE_NAME + ".Fields." + collName + "] == DBNull.Value ? null : " + dbType + ".Parse(dr[" + TABLE_NAME + ".Fields." + collName + "].ToString());";
            }
            return retVal;
        }

        private SqlCommand GetCommand(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            return cmd;
        }

        private string GetPK(string table)
        {

            string pk = "";
            DataTable pkTable = new DataTable();
            string sql = @"SELECT KCU.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
		AND KCU.TABLE_NAME=@tableName";

            SqlCommand cmd = GetCommand(sql);
            cmd.Parameters.AddWithValue("@tableName", table);
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            if (result != null)
                pk = result.ToString();

            return pk;
        }

        //#>
    }
}
